﻿using System.Collections.Generic;
using com.loto.Common.DBUtility;
using com.huigo.Framework.Model;
using System.Data.SqlClient;
using System;

namespace com.huigo.Framework.BLL
{
    public class CustInfo
    {
        public static long AddCustInfo(Model.CustInfo custInfo, Model.CustExtInfo extInfo)
        {
            using (SqlTransaction trans = SqlHelper.SQLTransaction(SqlHelper.MainSQLConnString))
            {
                try
                {
                    long custId = SqlServerDal.CustInfo.Insert(custInfo, trans);
                    if (custId > 0)
                    {
                        extInfo.ExtCustFid = custId;
                        SqlServerDal.CustExtInfo.Insert(extInfo, trans);
                        trans.Commit();
                    }
                    else
                    {
                        trans.Rollback();
                        custId = long.MinValue;
                    }
                    return custId;
                }
                catch
                {
                    trans.Rollback();
                    return long.MinValue;
                }
            }
        }

        public static List<Model.CustInfo> GetCustList(string custName, int pageSize, int pageIndex,DateTime start,DateTime end,string type,string userName)
        {
            List<string> fields = new List<string>() 
             {
                "CUST_ID",
                "CUST_NAME",
                "CUST_EMAIL",
                "TYPE_NAME",
                "CUST_FROM",
                "LEVEL_NAME",
                "CUST_TOTAL_FEE",
                "CUST_USEFUL_FEE",
                "CUST_TOTAL_POINT",
                "CUST_USEFUL_POINT",
                "CUST_CREATED_ON",
                "CUST_LAST_ACTIVED_ON",
                "CUST_STATUS",
                "CUST_MARK",
                "USER_REAL_NAME"
            };
            List<JoinStatement> joinList = new List<JoinStatement>()
            {
                new JoinStatement("LEFT","[HG_CUST_TYPE]","TYPE_ID=CUST_TYPE_FID"),
                new JoinStatement("LEFT","[HG_CUST_LEVEL]","LEVEL_ID=CUST_LEVEL_FID"),
                new JoinStatement("LEFT","HG_USER_INFO","USER_ID=CUST_USER_FID")
            };

            List<OrderByStatement> OrderByStatement = new List<OrderByStatement>() 
             {
                 new OrderByStatement("CUST_STATUS",true),
                 new OrderByStatement("CUST_CREATED_ON",false)
             };
            List<WhereStatement> whereList = new List<WhereStatement>();
            if (!string.IsNullOrEmpty(custName))
            {
                whereList.Add(new WhereStatement("(CUST_NAME", "LIKE", "'%" + custName + "%'", "OR"));
                whereList.Add(new WhereStatement("CUST_MARK", "LIKE", "'%" + custName + "%')", " AND "));
            }
            if (start!=DateTime.MinValue && end!=DateTime.MinValue)
            {
                whereList.Add(new WhereStatement("CUST_CREATED_ON", "BETWEEN", "'" + start + "' AND '" + end.AddDays(1) + "'", " AND "));
            }
            if (!string.IsNullOrEmpty(type))
            {
                whereList.Add(new WhereStatement("CUST_STATUS", "=", type, " AND  "));
            }
            if (!string.IsNullOrEmpty(userName))
            {
                List<string> filed = new List<string>()
                {
                    "USER_ID"
                };
                List<WhereStatement> where = new List<WhereStatement>();
                where.Add(new WhereStatement("USER_REAL_NAME", "=", "'" + userName + "'", "or"));
                where.Add(new WhereStatement("USER_NAME", "=", "'" + userName + "'", ""));
                List<Model.UserInfo> userList = SqlServerDal.UserInfo.GetItems(filed, null, where, null, null, null);
                if (userList.Count > 0)
                {
                    whereList.Add(new WhereStatement("CUST_USER_FID", "=", "" + userList[0].UserId + "", "AND"));
                }
                
            }            
            if (string.IsNullOrEmpty(custName) && start == DateTime.MinValue && end == DateTime.MinValue && string.IsNullOrEmpty(type) && string.IsNullOrEmpty(userName))
            {
                whereList.Add(new WhereStatement("CUST_STATUS", "=", "0", ""));
            }
            else
            {
                whereList.Add(new WhereStatement("CUST_STATUS", "IN", "(0,1,2)", ""));
            }
            List<Model.CustInfo> custList = null;
            int recordCount = 0;
            int pageCount = 0;
            custList = SqlServerDal.CustInfo.GetItemsByProc(fields, joinList, whereList, OrderByStatement, "[PROC_PAGINATION_OUT]", pageSize, pageIndex, out recordCount, out pageCount, null);
            custList.ForEach(delegate(Model.CustInfo info)
            {
                info.RecordCount = recordCount;
                info.PageCount = pageCount;
            });
            return custList;

        }

        public static Model.CustInfo GetCustInfoById(long id)
        {
            Model.CustInfo custInfo = new Model.CustInfo();
            custInfo.CustId = id;
            List<string> fields = new List<string>() 
             {
                "CUST_ID",
                "CUST_NAME",
                "CUST_EMAIL",
                "TYPE_NAME",
                "CUST_FROM",
                "LEVEL_NAME",
                "USER_REAL_NAME",
                "CUST_TOTAL_FEE",
                "CUST_USEFUL_FEE",
                "CUST_TOTAL_POINT",
                "CUST_USEFUL_POINT",
                "CUST_CREATED_ON",
                "CUST_LAST_ACTIVED_ON",
                "CUST_STATUS",
                "CUST_MARK",
            };
            List<JoinStatement> joinList = new List<JoinStatement>()
            {
                new JoinStatement("LEFT","[HG_CUST_TYPE]","TYPE_ID=CUST_TYPE_FID"),
                new JoinStatement("LEFT","[HG_CUST_LEVEL]","LEVEL_ID=CUST_LEVEL_FID"),
                new JoinStatement("LEFT","[HG_USER_INFO]","USER_ID=CUST_USER_FID"),
           };
            return SqlServerDal.CustInfo.GetItem(custInfo, fields, joinList, null);
        }

        public static bool CustIsExisted(long cid, string custName, string email)
        {
            bool isExist = false;
            List<Model.CustInfo> custInfo = new List<Model.CustInfo>();
            List<string> fields = new List<string>() 
            {
                "CUST_ID",
            };
            List<WhereStatement> whereList = new List<WhereStatement>()
            {
                new WhereStatement("CUST_NAME","=","'"+custName+"'","OR"),
                new WhereStatement("CUST_EMAIL","=","'"+email+"'","")
            };
            custInfo = SqlServerDal.CustInfo.GetItems(fields, whereList, null, null);
            if (custInfo != null && custInfo.Count > 0)
            {
                if (cid == long.MinValue)
                    isExist = true;
                else
                {
                    foreach (Model.CustInfo c in custInfo)
                    {
                        if (cid != c.CustId)
                            isExist = true;
                    }
                }
            }
            return isExist;
        }

        public static bool UpdateCustInfo(long cid, Model.CustInfo custInfo)
        {
            List<WhereStatement> whereList = new List<WhereStatement>()
            {
                    new WhereStatement("CUST_ID","=",cid.ToString(),"")
            };
            return SqlServerDal.CustInfo.Update(custInfo, whereList, null);
        }

        public static bool UpdateCustInfo(long cid, Model.CustInfo custInfo, Model.CustExtInfo extInfo)
        {
            List<WhereStatement> whereList = new List<WhereStatement>()
            {
                    new WhereStatement("CUST_ID","=",cid.ToString(),"")
            };
            using (SqlTransaction trans = SqlHelper.SQLTransaction(SqlHelper.MainSQLConnString))
            {
                try
                {
                    if (SqlServerDal.CustInfo.Update(custInfo, whereList, trans))
                    {
                        whereList = new List<WhereStatement>()
                        {
                            new WhereStatement("EXT_CUST_FID","=",cid.ToString(),"")
                        };
                        if (SqlServerDal.CustExtInfo.Update(extInfo, whereList, trans))
                            trans.Commit();
                        else
                        {
                            trans.Rollback();
                            return false;
                        }
                    }
                    else
                    {
                        trans.Rollback();
                        return false;
                    }
                    return true;
                }
                catch
                {
                    trans.Rollback();
                    return false;
                }
            }
        }
        /// <summary>
        /// 根据姓名查询用户信息
        /// </summary>
        /// <param name="custName"></param>
        /// <returns></returns>
        public static Model.CustInfo GetCustInfoByCustName(string custName)
        {
            List<string> fields = new List<string>() 
             {
                "CUST_ID",
                "CUST_NAME",
                "CUST_EMAIL",                
                "CUST_FROM",
                "CUST_TOTAL_FEE",
                "CUST_USEFUL_FEE",
                "CUST_TOTAL_POINT",
                "CUST_USEFUL_POINT",
                "CUST_CREATED_ON",
                "CUST_LAST_ACTIVED_ON",
                "CUST_STATUS",
                "CUST_MARK",
            };
            Model.CustInfo custInfo = new com.huigo.Framework.Model.CustInfo();
            custInfo.CustName = custName;
            return SqlServerDal.CustInfo.GetItem(custInfo, fields, null, null);
        }
        /// <summary>
        /// 经销商注册
        /// </summary>
        /// <param name="custInfo"></param>
        /// <param name="custExtInfo"></param>
        /// <returns></returns>
        public static long insertCustInfo(Model.CustInfo custInfo,Model.CustExtInfo custExt)
        {
            long resutlt = Convert.ToInt64(SqlServerDal.CustInfo.Insert(custInfo, null));
            if (resutlt > 0)
            {
                Model.CustExtInfo custExtInfo = new com.huigo.Framework.Model.CustExtInfo();
                custExtInfo.ExtCustFid = resutlt;
                custExtInfo.ExtContactName = custExt.ExtContactName;
                custExtInfo.ExtContactTel = custExt.ExtContactTel;
                custExtInfo.ExtContactOthers = custExt.ExtContactOthers;
                custExtInfo.ExtCompanyName = custExt.ExtCompanyName;
                custExtInfo.ExtCompanyZip = custExt.ExtCompanyZip;
                custExtInfo.ExtCompanyAddress = custExt.ExtCompanyAddress;
                custExtInfo.ExtCompanyTel = custExt.ExtCompanyTel;
                custExtInfo.ExtCompanyFax = custExt.ExtCompanyFax;
                custExtInfo.ExtCompanyEmail = custExt.ExtCompanyEmail;
                custExtInfo.ExtCompanyWebsite = custExt.ExtCompanyWebsite;
                custExtInfo.ExtCompanyReg = custExt.ExtCompanyReg;
                custExtInfo.ExtChargeName = custExt.ExtChargeName;
                custExtInfo.ExtFrom = custExt.ExtFrom;
                resutlt = CustExtInfo.insertCustExtInfo(custExtInfo);
            }
            return resutlt;
        }
        /// <summary>
        /// 根据邮箱查询用户信息
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public static Model.CustInfo getCustInfoByCustEmail(string email)
        {
            List<string> fields = new List<string>() 
             {
                "CUST_ID",
                "CUST_NAME",
                "CUST_EMAIL",                
                "CUST_FROM",
                "CUST_TOTAL_FEE",
                "CUST_USEFUL_FEE",
                "CUST_TOTAL_POINT",
                "CUST_USEFUL_POINT",
                "CUST_CREATED_ON",
                "CUST_LAST_ACTIVED_ON",
                "CUST_STATUS",
                "CUST_MARK",
            };
            Model.CustInfo custInfo = new com.huigo.Framework.Model.CustInfo();
            custInfo.CustEmail = email;
            return SqlServerDal.CustInfo.GetItem(custInfo, fields, null, null);
        }
        /// <summary>
        /// 用户名登录
        /// </summary>
        /// <param name="custName"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public static Model.CustInfo GetCustInfoByCustIdAndPwd(string custName, string pwd)
        {
            Model.CustInfo custInfo = new com.huigo.Framework.Model.CustInfo();
            List<string> fileds = new List<string>()
            {
                "CUST_ID",
                "CUST_NAME",
                "CUST_EMAIL",                
                "CUST_FROM",
                "CUST_TOTAL_FEE",
                "CUST_USEFUL_FEE",
                "CUST_TOTAL_POINT",
                "CUST_USEFUL_POINT",
                "CUST_CREATED_ON",
                "CUST_LAST_ACTIVED_ON",
                "CUST_STATUS",
                "CUST_MARK",
            };
            custInfo.CustName = custName;
            custInfo.CustPwd = pwd;
            custInfo.CustStatus = 1;
            return SqlServerDal.CustInfo.GetItem(custInfo, fileds, null, null);
        }
        /// <summary>
        /// 用邮箱登录
        /// </summary>
        /// <param name="email"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public static Model.CustInfo GetCustInfoByCustEmailAndPwd(string email, string pwd)
        {
            Model.CustInfo custInfo = new com.huigo.Framework.Model.CustInfo();
            List<string> fileds = new List<string>()
            {
                "CUST_ID",
                "CUST_NAME",
                "CUST_EMAIL",                
                "CUST_FROM",
                "CUST_TOTAL_FEE",
                "CUST_USEFUL_FEE",
                "CUST_TOTAL_POINT",
                "CUST_USEFUL_POINT",
                "CUST_CREATED_ON",
                "CUST_LAST_ACTIVED_ON",
                "CUST_STATUS",
                "CUST_MARK",
            };
            custInfo.CustEmail = email;
            custInfo.CustPwd = pwd;
            custInfo.CustStatus = 1;
            return SqlServerDal.CustInfo.GetItem(custInfo, fileds, null, null);
        }

        /// <summary>
        /// 根据ID查询用户余额
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public static Model.CustInfo GetCustInfoByCustId(long custId)
        {
            List<string> fields = new List<string>() 
             {
                "CUST_TOTAL_FEE",
                "CUST_USEFUL_FEE",
                "CUST_PWD"
            };
            Model.CustInfo custInfo = new com.huigo.Framework.Model.CustInfo();
            custInfo.CustId = custId;
            return SqlServerDal.CustInfo.GetItem(custInfo, fields, null, null);
        }
        /// <summary>
        ///  修改用户的可用余额
        /// </summary>
        /// <param name="custFid"></param>
        /// <param name="weightFee"></param>
        /// <returns></returns>
        public static int updateFee(long custFid, decimal weightFee, SqlTransaction trans)
        {
            Model.CustInfo custInfo = GetCustInfoByCustId(custFid);
            Model.CustInfo cust = new com.huigo.Framework.Model.CustInfo();
            if (custInfo != null)
            {
                cust.CustTotalFee = custInfo.CustTotalFee + weightFee;
                cust.CustUsefulFee = custInfo.CustUsefulFee + weightFee;
                List<WhereStatement> whereList = new List<WhereStatement>()
                {
                    new WhereStatement("CUST_ID","=",custFid.ToString(),"")
                };
                return Convert.ToInt32(SqlServerDal.CustInfo.Update(cust, whereList, null));
            }
            else
            {
                return 0;
            }

        }
        /// <summary>
        /// 修改用户密码
        /// </summary>
        /// <param name="custFid"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public static int updatePwd(long custFid, string pwd)
        {
            Model.CustInfo custInfo = GetCustInfoByCustId(custFid);
            if (custInfo != null)
            {
                Model.CustInfo cust = new com.huigo.Framework.Model.CustInfo();
                cust.CustPwd = pwd;
                List<WhereStatement> whereList = new List<WhereStatement>()
                {
                    new WhereStatement("CUST_ID","=",custFid.ToString(),"")
                };
                return Convert.ToInt32(SqlServerDal.CustInfo.Update(cust,whereList,null));
            }
            else
            {
                return 0;
            }
        }
        /// <summary>
        /// 修改用户最后登录时间
        /// </summary>
        /// <param name="custId"></param>
        /// <returns></returns>
        public static bool UpdateCustLastTime(long custId)
        {
            Model.CustInfo custInfo = new com.huigo.Framework.Model.CustInfo();
            custInfo.CustLastActivedOn = DateTime.Now;
            List<WhereStatement> whereList = new List<WhereStatement>()
            {
                new WhereStatement("CUST_ID","=",""+custId+"","")
            };
            return SqlServerDal.CustInfo.Update(custInfo, whereList, null);
        }
        //销售员查询经销商
        public static List<Model.CustInfo> GetCustListBySales(string custName, int pageSize, int pageIndex, DateTime start, DateTime end, string contactName, string companyName)
        {
            List<string> fields = new List<string>() 
             {
                "CUST_ID",
                "CUST_NAME",
                "CUST_EMAIL",
                "TYPE_NAME",
                "CUST_FROM",
                "LEVEL_NAME",
                "CUST_TOTAL_FEE",
                "CUST_USEFUL_FEE",
                "CUST_TOTAL_POINT",
                "CUST_USEFUL_POINT",
                "CUST_CREATED_ON",
                "CUST_LAST_ACTIVED_ON",
                "CUST_STATUS",
                "CUST_MARK",
                "EXT_CONTACT_NAME",
                "EXT_COMPANY_NAME"
            };
            List<JoinStatement> joinList = new List<JoinStatement>()
            {
                new JoinStatement("LEFT","[HG_CUST_TYPE]","TYPE_ID=CUST_TYPE_FID"),
                new JoinStatement("LEFT","[HG_CUST_LEVEL]","LEVEL_ID=CUST_LEVEL_FID"),
                new JoinStatement("LEFT","HG_CUST_EXT_INFO","EXT_CUST_FID=CUST_ID")
            };

            List<OrderByStatement> OrderByStatement = new List<OrderByStatement>() 
             {
                 new OrderByStatement("CUST_STATUS",true),
                 new OrderByStatement("CUST_CREATED_ON",false)
             };
            List<WhereStatement> whereList = new List<WhereStatement>();
            if (!string.IsNullOrEmpty(custName))
            {
                whereList.Add(new WhereStatement("(CUST_NAME", "LIKE", "'%" + custName + "%'", "OR"));
                whereList.Add(new WhereStatement("CUST_MARK", "LIKE", "'%" + custName + "%')", " AND "));
            }
            if (start != DateTime.MinValue && end != DateTime.MinValue)
            {
                whereList.Add(new WhereStatement("CUST_CREATED_ON", "BETWEEN", "'" + start + "' AND '" + end.AddDays(1) + "'", " AND "));
            }
            if (!string.IsNullOrEmpty(contactName))
            {
                whereList.Add(new WhereStatement("EXT_CONTACT_NAME", "like", "'%" + contactName + "%'", "and"));
            }
            if (!string.IsNullOrEmpty(companyName))
            {
                whereList.Add(new WhereStatement("EXT_COMPANY_NAME", "like", "'%" + companyName + "%'", "and"));
            }
            if (string.IsNullOrEmpty(custName) && start == DateTime.MinValue && end == DateTime.MinValue && string.IsNullOrEmpty(contactName) && string.IsNullOrEmpty(companyName))
            {
                whereList.Add(new WhereStatement("CUST_STATUS", "=", "0", ""));
            }
            else
            {
                whereList.Add(new WhereStatement("CUST_STATUS", "in", "(0,1,2)", ""));
            }
            List<Model.CustInfo> custList = null;
            int recordCount = 0;
            int pageCount = 0;
            custList = SqlServerDal.CustInfo.GetItemsByProc(fields, joinList, whereList, OrderByStatement, "[PROC_PAGINATION_OUT]", pageSize, pageIndex, out recordCount, out pageCount, null);
            custList.ForEach(delegate(Model.CustInfo info)
            {
                info.RecordCount = recordCount;
                info.PageCount = pageCount;
            });
            return custList;
        }
    }
}
